# SSH协议通用性巡检脚本
# 版本：1.0
# 支持：SSH协议的设备
# 作者：didiplus


import paramiko
import pandas as pd
from datetime import datetime
import re,json
import os,logging

logging.basicConfig(level=logging.INFO, format="%(asctime)s - %(levelname)s - %(message)s")




class SSHClientWrapper:
    def __init__(self, server):
        """初始化SSH客户端"""
        self.server = server
        self.client = None
        if not all(key in server for key in ["hostname", "port", "username", "password"]):
            logging.error("服务器配置不完整，缺少必要字段")
            raise ValueError("服务器配置不完整，缺少必要字段")

    def verify_host_key(self):
        """验证主机密钥"""
        try:
            self.client.load_system_host_keys()
            self.client.set_missing_host_key_policy(paramiko.RejectPolicy())  # 拒绝未知主机密钥
        except Exception as e:
            logging.error(f"主机密钥验证失败: {str(e)}")
            return False
        return True

    def connect(self):
        """建立SSH连接"""
        self.client = paramiko.SSHClient()
        if not self.verify_host_key():
            self.close()
            return False

        try:
            self.client.connect(
                hostname=self.server["hostname"],
                port=self.server["port"],
                username=self.server["username"],
                password=self.server["password"]
            )
            logging.info(f"成功连接到 {self.server['hostname']}")
            return True
        except paramiko.AuthenticationException:
            logging.error(f"认证失败: 无法连接到 {self.server['hostname']}")
        except paramiko.SSHException as e:
            logging.error(f"SSH连接错误: {str(e)}")
        except Exception as e:
            logging.error(f"连接 {self.server['hostname']} 失败: {str(e)}")
        finally:
            if self.client and (self.client.get_transport() is None or not self.client.get_transport().is_active()):
                self.close()
        return False

    def execute_command(self, command):
        """执行命令并返回标准输出和错误输出"""
        if not self.client or not self.client.get_transport().is_active():
            logging.error("SSH连接未建立")
            return None, "SSH连接未建立"

        try:
            stdin, stdout, stderr = self.client.exec_command(command)
            # 逐行读取输出，减少内存占用
            output = "".join(stdout)
            error = "".join(stderr)
            return output, error
        except Exception as e:
            logging.error(f"命令执行失败: {command}, 错误信息: {str(e)}")
            return None, str(e)
    

    def validate_regex(self,pattern):
        """验证正则表达式的合法性"""
        try:
            re.compile(pattern)
            return True
        except re.error:
            return False

    def close(self):
        """关闭SSH连接"""
        if self.client:
            self.client.close()
            logging.info(f"关闭连接到 {self.server['hostname']}")


def load_json_file(file_path):
    """
    从指定路径读取JSON文件
    
    参数:
        file_path (str): JSON文件路径
    
    返回:
        list: JSON文件内容，若文件不存在或格式错误则返回空字典
    """
    try:
        with open(file_path, 'r', encoding='utf-8') as file:
            data = json.load(file)
        return data if isinstance(data, list) else []
    except FileNotFoundError:
        logging.error(f"文件未找到: {file_path}")
        return  []
    except json.JSONDecodeError:
        logging.error(f"文件格式错误: {file_path}")
        return  []
    except PermissionError:
        logging.error(f"无权限访问文件: {file_path}")
        return  []
    except UnicodeDecodeError:
        logging.error(f"文件编码错误: {file_path}")
        return  []
    except Exception as e:
        logging.error(f"加载文件时发生未知错误: {file_path}, 错误信息: {e}")
        return  []

def load_check_items():
    """从当前目录读取巡检项目"""
    return load_json_file('check_item.json')

def load_servers_items():
    """从当前目录读取服务器配置"""
    return load_json_file('servers.json')

def validate_regex(pattern):
    """验证正则表达式的合法性"""
    try:
        re.compile(pattern)
        return True
    except re.error:
        return False



def parse_output(output, regex_pattern, name):
    """解析输出并提取数据"""
    if not validate_regex(regex_pattern):
        logging.error(f"正则表达式无效: {regex_pattern}")
        return None
    
    match = re.search(regex_pattern, output)
    if match:
        logging.info(f"匹配到数据: {match.groups()}")
        return match.groups()
    else:
        logging.warning(f"未匹配到数据: {name}")
        return {}



def execute_inspection(client, config):
    """执行单个巡检项并解析结果"""
    command = config.get("command")
    regex_pattern = config.get("regex_pattern")
    name = config.get("name")
    
    if not all([command, regex_pattern, name]):
        logging.error("配置不完整，缺少必要字段")
        return None
    
    
    output, error = client.execute_command(command)
    if error:
        logging.error(f"命令执行错误: {command}\n{error}")
        return None
    
    return parse_output(output, regex_pattern, name)

def format_result(config, result):
    if "regex_groups" in config:
        params = dict(zip(config["regex_groups"], result))
        return config["format_template"].format(**params)
    else:
        return config["format_template"].format(*result)


def generate_report(data,check_item ,filename):
    """生成合并IP单元格的巡检报告"""
    report_data = []
    for ip, inspections in data.items():
        ip_entry = {
            "IP": ip,
            "巡检项": [],
            "结果": []
        }
        for item_name, result in inspections.items():
            ip_entry["巡检项"].append(item_name)
            ip_entry["结果"].append(
                format_result(
                    next(c for c in check_item if c["name"] == item_name),
                    result
                )
            )
        report_data.append(ip_entry)

    # 转换为二维表格（保留空值用于单元格合并）
    max_items = max(len(entry["巡检项"]) for entry in report_data)
    rows = []
    for entry in report_data:
        rows.append([entry["IP"], entry["巡检项"][0], entry["结果"][0]])
        for i in range(1, len(entry["巡检项"])):
            rows.append(["", entry["巡检项"][i], entry["结果"][i]])

    df = pd.DataFrame(rows, columns=["IP", "巡检项", "结果"])

    # 生成Excel文件
    with pd.ExcelWriter(filename, engine='xlsxwriter') as writer:
        df.to_excel(writer, index=False, sheet_name='巡检报告')
        
        workbook = writer.book
        worksheet = writer.sheets['巡检报告']
        
        # 设置列宽
        worksheet.set_column('A:A', 15)  # IP列
        worksheet.set_column('B:B', 25)  # 巡检项列
        worksheet.set_column('C:C', 45)  # 结果列
        
        # 标题格式
        header_format = workbook.add_format({
            'bold': True, 
            'bg_color': '#4CAF50',
            'font_color': 'white',
            'border': 1,
            'valign': 'vcenter'
        })
        for col_num, value in enumerate(df.columns.values):
            worksheet.write(0, col_num, value, header_format)

        # 数据格式
        data_format = workbook.add_format({
            'border': 1,
            'valign': 'top'
        })
        for row in range(1, len(df)+1):
            for col in range(3):
                worksheet.write(row, col, df.iat[row-1, col], data_format)

        # 合并IP单元格
        ip_col = 0
        current_ip = None
        merge_start = 1
        
        for row_num in range(1, len(df)+1):
            cell_value = df.iat[row_num-1, ip_col]
            if cell_value:
                if current_ip is not None and merge_start < row_num:
                    worksheet.merge_range(
                        merge_start, ip_col,
                        row_num-1, ip_col,
                        current_ip, 
                        workbook.add_format({
                            'valign': 'vcenter',
                            'border': 1
                        })
                    )
                current_ip = cell_value
                merge_start = row_num
                
        # 处理最后一个IP
        if current_ip is not None and merge_start <= len(df):
            worksheet.merge_range(
                merge_start, ip_col,
                len(df), ip_col,
                current_ip,
                workbook.add_format({
                    'valign': 'vcenter',
                    'border': 1
                })
            )

    print(f"报告已生成: {os.path.abspath(filename)}")

def main():
    all_results = {}
    check_items = load_check_items()
    server_items = load_servers_items()
    
    for server in server_items:

        ssh_client = SSHClientWrapper(server)
        if ssh_client.connect():
            logging.info(f"正在检查 {server['hostname']}...")
            results = {}
            for item in check_items:
                value = execute_inspection(ssh_client, item)
                if value:
                    results[item["name"]] = value
            all_results[server['hostname']] = results

    if all_results:
        filename = f"server_inspection_{datetime.now().strftime('%Y%m%d_%H%M')}.xlsx"
        generate_report(all_results,check_items, filename)
    else:
        print("未获取到任何服务器数据")



if __name__ == "__main__":
    main()
